package com.ujiuye.dao.impl;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ujiuye.dao.CourseDao;
import com.ujiuye.entity.Course;
import com.ujiuye.entity.Coursedetail;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * CompanyName: IT优就业<br/>
 * Datetime: 2021/7/2 10:32<br/>
 *
 * @author TanJian
 */
public class CourseDaoImpl implements CourseDao {

    QueryRunner qr = new QueryRunner(new ComboPooledDataSource());

    @Override
    public int addCourse(Course c) {
        String sql = "insert into course values(null,?,?,?,?,?,?,?,?)";
        try {
            return qr.update(sql,c.getCourseName(), c.getDescs(), c.getCourseType(), c.getCourseImage(),
                    c.getCourseVideo(), c.getCoursePrice(), c.getStatus(), c.getCreateTime());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int totalCount(String courseName) {

        StringBuilder sql = new StringBuilder("select count(*) from course where 1=1");

        if(courseName!=null && !"".equals(courseName)){
            sql.append(" and coursename like '%"+courseName+"%'");
        }

        try {
            Long num = qr.query(sql.toString(),new ScalarHandler<Long>());
            return num.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }

    @Override
    public List<Course> selectAllCourse(int currentPage,int startIndex, int pageSize, String courseName) {

        StringBuilder sql = new StringBuilder("select * from course where 1=1");

        if(courseName!=null && !"".equals(courseName)){
            sql.append(" and coursename like '%"+courseName+"%'");

            startIndex = (currentPage-1) * pageSize;
        }

        sql.append(" limit ?,?");

        try {
            return  qr.query(sql.toString(),new BeanListHandler<Course>(Course.class),startIndex,pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return null;
    }

    @Override
    public int delAll(String cids) {
        String sql = "delete from course where cid in("+cids+")";
        try {
            return   qr.update(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updateCourse(Course c) {
        String sql = "update course set coursename = ?,descs = ?,coursetype = ?,courseimage = ?," +
                "coursevideo = ?,courseprice = ?,status = ?,createtime = ? where cid = ?";
        try {
            return qr.update(sql,c.getCourseName(), c.getDescs(), c.getCourseType(), c.getCourseImage(),
                    c.getCourseVideo(), c.getCoursePrice(), c.getStatus(), new Date(),c.getCid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int addCourseDetail(Coursedetail cd) {
        String sql = "insert into coursedetail(name,type,url,start_data,cid) values(?,?,?,?,?)";
        try {
          return   qr.update(sql,cd.getName(),cd.getType(),cd.getUrl(),cd.getStart_data(),cd.getCid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Course> findCourseByType(int courseType) {
        String sql = "select * from course where courseType = ?";
        try {
           return qr.query(sql,new BeanListHandler<Course>(Course.class),courseType);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int beforeTotalCount(String courseName, int courseType) {

        StringBuilder sql = new StringBuilder("select count(*) from course where 1=1");
        if(courseName!=null && !"".equals(courseName)){
            sql.append(" and courseName like '%"+courseName+"%'");
        }
        if(courseType!=0){
            sql.append(" and courseType="+courseType);
        }

        try {
            Long num =  qr.query(sql.toString(),new ScalarHandler<Long>());
            return num.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Course> beforeSelectAllCourse(String courseName, int courseType, int startIndex, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from course where 1=1");
        if(courseName!=null && !"".equals(courseName)){
            sql.append(" and courseName like '%"+courseName+"%'");
        }
        if(courseType!=0){
            sql.append(" and courseType="+courseType);
        }

        sql.append(" limit ?,?");

        try {
            return  qr.query(sql.toString(),new BeanListHandler<Course>(Course.class),startIndex,pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public Course findCourse(int cid) {
        String sql ="select * from course where cid = ?";
        try {
             //查询课程信息
            Course course = qr.query(sql,new BeanHandler<Course>(Course.class),cid);
            //查询课程详细信息
            String sql2 = "select * from coursedetail where cid = ?";
            List<Coursedetail> list = qr.query(sql2,new BeanListHandler<Coursedetail>(Coursedetail.class),cid);
            course.setList(list);
            return course;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Map<String, List<Coursedetail>> findCourseDetail(int cid) {

        String sql = "select type from coursedetail where cid = ? group by type";
        //声明map集合
        Map<String,List<Coursedetail>> map = new HashMap<>();
        try {
            List<Coursedetail> list = qr.query(sql, new BeanListHandler<Coursedetail>(Coursedetail.class), cid);

            //遍历list集合
            for(Coursedetail cd:list){
                String sql2 = "select * from Coursedetail where type = ? and cid = ?";
                List<Coursedetail> list2 = qr.query(sql2, new BeanListHandler<Coursedetail>(Coursedetail.class), cd.getType(), cid);
                map.put(cd.getType(),list2);
            }
            return map;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Course> findCourseByUid(int uid) {
        String sql = "select * from course where cid in(select cid from course_user where uid = ?)";
        try {
            return qr.query(sql,new BeanListHandler<Course>(Course.class),uid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


}

